package com.captjack.common.util.poi;

import com.captjack.common.util.io.IoCommonUtil;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author cc
 * @date 2017/7/3
 * 操作excel工具
 */
public final class ExcelUtils {

    /**
     * 后缀
     */
    public static final String XLS_2003_SUFFIX = ".xls";
    public static final String XLSX_2007_SUFFIX = ".xlsx";

    public static List<Map<Integer, Object>> readData(File file) throws IOException, InvalidFormatException {
        return readData(file, 0);
    }

    /**
     * 读取Excel的内容，返回List<Map<String, Object>>
     * 默认excel表格中只有一个sheet
     *
     * @param file       读取数据的源Excel
     * @param sheetIndex 读取的sheetIndex
     * @return 读出的Excel中数据的内容
     * @throws IOException            异常
     * @throws InvalidFormatException 异常
     */
    public static List<Map<Integer, Object>> readData(File file, int sheetIndex) throws IOException, InvalidFormatException {
        if (file == null || !file.exists() || file.length() == 0) {
            throw new IOException("getData failed because file is unavailable!");
        }
        String fileName = file.getName();
        if (fileName.endsWith(XLS_2003_SUFFIX)) {
            return readXls(file, sheetIndex);
        } else if (fileName.endsWith(XLSX_2007_SUFFIX)) {
            return readXlsx(file, sheetIndex);
        } else {
            throw new IllegalArgumentException("error file type !" + fileName);
        }
    }

    /**
     * 读取Excel的内容，返回List<Map<String, Object>>
     * 默认excel表格中只有一个sheet
     *
     * @param file       读取数据的源Excel
     * @param sheetIndex 读取的sheetIndex
     * @return 读出的Excel中数据的内容
     * @throws IOException            异常
     * @throws InvalidFormatException 异常
     */
    private static List<Map<Integer, Object>> readXlsx(File file, int sheetIndex) throws IOException, InvalidFormatException {
        List<Map<Integer, Object>> result = new ArrayList<>();
        // 打开HSSFWorkbook
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
        // 获取excel中的第一个sheet，通常情况下默认一个sheet
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
        // 根据索引遍历所有sheet中的所有行
        for (int rowIndex = 0; rowIndex <= xssfSheet.getLastRowNum(); rowIndex++) {
            // 获取当前行对象
            XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
            // 同时创建该行的map
            Map<Integer, Object> map = new LinkedHashMap<>();
            // 遍历该行的所有列
            for (int columnIndex = 0; columnIndex <= xssfRow.getLastCellNum(); columnIndex++) {
                map.put(columnIndex, readCellValue(xssfRow.getCell(columnIndex)));
            }
            result.add(rowIndex, map);
        }
        return result;
    }

    /**
     * 读取Excel的内容，返回List<Map<String, Object>>
     * 默认excel表格中只有一个sheet
     *
     * @param file       读取数据的源Excel
     * @param sheetIndex 读取的sheetIndex
     * @return 读出的Excel中数据的内容
     * @throws IOException 异常
     */
    private static List<Map<Integer, Object>> readXls(File file, int sheetIndex) throws IOException {
        List<Map<Integer, Object>> result = new ArrayList<>();
        // 缓冲输入流
        BufferedInputStream bufferedInputStream = null;
        try {
            bufferedInputStream = new BufferedInputStream(new FileInputStream(file));
            // 打开HSSFWorkbook
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(bufferedInputStream);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
            // 获取excel中的第一个sheet，通常情况下默认一个sheet
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
            // 根据索引遍历所有sheet中的所有行
            for (int rowIndex = 0; rowIndex <= hssfSheet.getLastRowNum(); rowIndex++) {
                // 获取当前行对象
                HSSFRow hssfRow = hssfSheet.getRow(rowIndex);
                // 同时创建该行的map
                Map<Integer, Object> map = new LinkedHashMap<>();
                // 遍历该行的所有列
                for (int columnIndex = 0; columnIndex <= hssfRow.getLastCellNum(); columnIndex++) {
                    map.put(columnIndex, readCellValue(hssfRow.getCell(columnIndex)));
                }
                result.add(rowIndex, map);
            }
        } finally {
            IoCommonUtil.closeStream(bufferedInputStream);
        }
        return result;
    }

    /**
     * 读取单元格值
     *
     * @param cell 单元格
     * @return 值
     */
    private static Object readCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        Object result = null;
        switch (cell.getCellType()) {
            case STRING:
                result = cell.getStringCellValue();
                break;
            case NUMERIC:
                result = cell.getNumericCellValue();
                break;
            case FORMULA:
                result = cell.getCellFormula();
                break;
            case BLANK:
                break;
            case ERROR:
                break;
            case BOOLEAN:
                result = cell.getBooleanCellValue();
                break;
            default:
                break;
        }
        return result;
    }

    /**
     * 将Map集合生成Excel，表头需要手动初始化
     * 所有map中存储的数据格式需要保持一致（即所有map中需要有相同数量和名字的key）
     * List中的map可以看作是excel的一行数据，而map中的key-value，对应excel一行数据的列标识-列值
     *
     * @param exportList 需要导出的集合
     * @param <T>        无具体意义，为适应多种map导出
     * @param exportPath 导出文件的路径
     */
    public static <T> void exportExcelByMap(Iterable<Map<T, Object>> exportList, String exportPath) throws Exception {
        Workbook workbook = exportPath.endsWith(XLS_2003_SUFFIX) ? new HSSFWorkbook() : new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("list");
        int i = 0;
        // 遍历集合列表
        for (Map<T, Object> map : exportList) {
            // 创建excel中的一行
            Row row = sheet.createRow(i++);
            int index = 0;
            // 遍历map中的数据
            for (Map.Entry<T, Object> entry : map.entrySet()) {
                row.createCell(index++).setCellValue(entry.getValue() == null ? StringUtils.EMPTY : entry.getValue().toString());
            }
        }
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File(exportPath));
            // 写入文件
            workbook.write(outputStream);
        } finally {
            IoCommonUtil.closeStream(outputStream);
        }
    }

    /**
     * 将实体集合生成Excel，表头需要手动初始化
     *
     * @param exportList 需要导出的集合
     * @param exportPath 导出文件的路径
     * @param <T>        范型
     * @throws Exception 异常
     */
    public static <T> void exportExcelByEntity(Iterable<T> exportList, String exportPath) throws Exception {
        Workbook workbook = exportPath.endsWith(XLS_2003_SUFFIX) ? new HSSFWorkbook() : new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("list");
        int i = 0;
        // 遍历集合列表
        for (T t : exportList) {
            // 创建excel中的一行
            Row row = sheet.createRow(i++);
            // 转换entity为map
            Map<String, String> map = BeanUtils.describe(t);
            // 移除class字段
            map.remove("class");
            int index = 0;
            // 遍历map中的数据
            for (Map.Entry<String, String> entry : map.entrySet()) {
                row.createCell(index++).setCellValue(entry.getValue() == null ? StringUtils.EMPTY : entry.getValue());
            }
        }
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File(exportPath));
            // 写入文件
            workbook.write(outputStream);
        } finally {
            IoCommonUtil.closeStream(outputStream);
        }
    }

}